package dbops

import (
	"database/sql"
	"gitee.com/Cookie_XiaoD/video_server/api/defs"
	"gitee.com/Cookie_XiaoD/video_server/utils"
	_ "github.com/go-sql-driver/mysql" //匿名导入
	"log"
	"strconv"
	"time"
)

func AddUserCredential(loginName string, pwd string) (succeess bool, err error) {
	stmt, err := dbConn.Prepare("INSERT INTO sys_user (login_name,pwd,gmt_create) VALUES (?,?,utc_timestamp(3))")
	if err != nil {
		return
	}
	defer stmt.Close()
	result, err := stmt.Exec(loginName, utils.MD5(pwd))
	if err != nil {
		return
	}
	count, err := result.RowsAffected()
	if err != nil {
		return
	}
	succeess = count > 0
	return
}

func GetUserCredential(loginName string) (string, error) {
	stmt, err := dbConn.Prepare("SELECT pwd FROM sys_user WHERE login_name = ?")
	if err != nil {
		return "", err
	}
	var pwd string
	if err = stmt.QueryRow(loginName).Scan(&pwd); err != nil && err != sql.ErrNoRows {
		return "", err
	}

	return pwd, nil
}

func DelUser(loginName string, pwd string) (success bool, err error) {
	stmt, err := dbConn.Prepare("DELETE FROM sys_user WHERE login_name=? and pwd=?")
	if err != nil {
		return
	}
	defer stmt.Close()
	result, err := stmt.Exec(loginName, pwd)
	if err != nil {
		return
	}
	count, err := result.RowsAffected()
	if err != nil {
		return
	}
	success = count > 0
	return

}

func GetUserInfo(uid int) (*defs.UserInfo, error) {
	stmtOut, err := dbConn.Prepare(`SELECT id,login_name,pwd,gmt_create,gmt_modified FROM sys_user WHERE id=?`)
	defer stmtOut.Close()
	if err != nil {
		return nil, err
	}
	var userinfo *defs.UserInfo = &defs.UserInfo{}
	log.Println("准备查询" + strconv.FormatBool((userinfo == nil)))
	if err = stmtOut.QueryRow(uid).Scan(&userinfo.Id, &userinfo.Login_Name, &userinfo.Pwd, &userinfo.GMT_Create, &userinfo.GMT_Modified); err != nil {
		return nil, err
	}
	log.Println(userinfo.GMT_Create.Local())
	return userinfo, nil
}

//视频相关
func AddNewVideo(aid int, name string) (result *defs.VideoInfo, err error) {
	vid, err := utils.NewUUID()
	if err != nil {
		return
	}
	t := time.Now()
	ctime := t.Format("2006-01-02 15:04:05")
	stmtIns, err := dbConn.Prepare("INSERT INTO sys_video_info(id,author_id,name,display_ctime,gmt_create) VALUES(?, ?,?,?,UTC_TIMESTAMP(3))")
	defer stmtIns.Close()
	if err != nil {
		return
	}
	_, err = stmtIns.Exec(vid, aid, name, ctime)
	if err != nil {
		return
	}

	result = &defs.VideoInfo{
		Id:           vid,
		AuthorId:     aid,
		DisplayCtime: ctime,
		Name:         name,
	}

	return

}

func DelVideo(vid string) (success bool, err error) {
	stmtDel, err := dbConn.Prepare("DELETE FROM sys_video_info WHERE id=?")
	defer stmtDel.Close()
	if err != nil {
		return
	}
	result, err := stmtDel.Exec(vid)
	if err != nil {
		return
	}
	count, err := result.RowsAffected()
	if err != nil {
		return
	}
	return count > 0, nil

}

func GetVideoInfo(vid string) (video *defs.VideoInfo, err error) {
	stmtGet, err := dbConn.Prepare(`SELECT
		sys_video_info.author_id,
		sys_video_info.name,
		sys_video_info.display_ctime
	FROM
	sys_video_info
	WHERE id=?
	`)
	defer stmtGet.Close()

	if err != nil {
		return
	}
	var (
		aid   int
		name  string
		ctime string
	)

	if err = stmtGet.QueryRow(vid).Scan(&aid, &name, &ctime); err != nil && err != sql.ErrNoRows {
		return
	}

	video = &defs.VideoInfo{
		Id:           vid,
		Name:         name,
		DisplayCtime: ctime,
		AuthorId:     aid,
	}

	return

}

//评论相关
func AddComment(uid int, vid string, comment string) error {
	stmtIn, err := dbConn.Prepare("INSERT INTO sys_comment (id,video_id,author_id,content,gmt_create) VALUES(?.?,?,?,UTC_TIMESTAMP(3))")
	defer stmtIn.Close()
	if err != nil {
		return err
	}

	cid, err := utils.NewUUID()
	if err != nil {
		return err
	}

	if _,err=stmtIn.Exec(cid,vid,uid,comment);err!=nil{
		return err
	}
	return nil
}

func ListComments(vid string, from, to time.Time) ([]*defs.Comment,error) {
	sql:=`SELECT c.id,u.login_name,c.content,c.gmt_create FROM 
sys_comment c
INNER JOIN sys_user u on c.author_id=u.id
where c.video_id=? AND c.gmt_create>? and c.gmt_create<=?`

	stmtOut,err:=dbConn.Prepare(sql)
	if err!=nil{
		return nil,err
	}
	log.Println("准备查询评论"+sql)
	rows,err:=stmtOut.Query(vid,from.String(),to.String())
	//rows,err:=stmtOut.Query(vid)
	if err!=nil{
		return nil,err
	}

	var result []*defs.Comment

	for rows.Next(){
		var(
			cid string
			authorName string
			content string
			createTime time.Time
		)
		err=rows.Scan(&cid,&authorName,&content,&createTime)
		if err!=nil{
			return nil,err
		}
		comment:=&defs.Comment{
			Id:cid,
			UserName:authorName,
			Content:content,
			CreateTime:createTime,
		}
		result=append(result,comment)
	}
	return result,nil
}